﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Data.Common;
using System.Configuration;


namespace SalaryMS
{
    class DBSession
    {

            public static void SetConfiguration(string key, string value)
            {
                AppSettingsSection appSection = null;               //AppSection对象

                appSection = (AppSettingsSection) ConfigurationManager.GetSection("appSettings");

                appSection.Settings[key].Value = value;

            }

            public static string GetConfiguration(string key)
            {
                return ConfigurationManager.AppSettings[key];
            }

            public static void SetDataSource(string datafile)
            {
                SetConfiguration("dataFile", datafile);
            }

            public static string GetSataSourcePath()
            {
                return GetConfiguration("dataFile");
            }

            /// <summary>  
            /// 创建SQLite数据库文件  
            /// </summary>  
            /// <param name="dbPath">要创建的SQLite数据库文件路径</param>  
            public static void CreateDB(List<String> tabschemas)  
            {  
                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + GetConfiguration("dataFile")) ) 
                {  
                    connection.Open();  
                    using (SQLiteCommand command = new SQLiteCommand(connection))  
                    {
                        foreach (string tabschema in tabschemas)
                        {
                            command.CommandText = tabschema;
                            command.ExecuteNonQuery();
                        }
                   }  
                }  
            }
  
            /// <summary>  
            /// 对SQLite数据库执行增删改操作，返回受影响的行数。  
            /// </summary>  
            /// <param name="sql">要执行的增删改的SQL语句</param>  
            /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
            /// <returns></returns>  
            public static int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)  
            {  
                int affectedRows = 0;
                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + GetConfiguration("dataFile")))  
                {  
                    connection.Open();  
                    using (DbTransaction transaction = connection.BeginTransaction())  
                   {  
                        using (SQLiteCommand command = new SQLiteCommand(connection))  
                        {  
                            command.CommandText = sql;  
                           if (parameters != null)  
                            {  
                                command.Parameters.AddRange(parameters);  
                            }  
                            affectedRows = command.ExecuteNonQuery();  
                        }  
                        transaction.Commit();  
                    }  
                }  
                return affectedRows;  
            }  

            /// <summary>  
            /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例  
            /// </summary>  
            /// <param name="sql">要执行的查询语句</param>  
            /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
            /// <returns></returns>  
            public static SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)  
            {
                SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + GetConfiguration("dataFile"));  
                SQLiteCommand command = new SQLiteCommand(sql, connection);  
                if (parameters != null)  
                {  
                    command.Parameters.AddRange(parameters);  
                }  
                connection.Open();  
                return command.ExecuteReader(CommandBehavior.CloseConnection);  
            }  

            /// <summary>  
            /// 执行一个查询语句，返回一个包含查询结果的DataTable  
            /// </summary>  
            /// <param name="sql">要执行的查询语句</param>  
           /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
            /// <returns></returns>  
           public static DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)  
            {
                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + GetConfiguration("dataFile")))  
              {  
                   using (SQLiteCommand command = new SQLiteCommand(sql, connection))  
                   {  
                      if (parameters != null)  
                       {  
                            command.Parameters.AddRange(parameters);  
                       }  
                      SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);  
                      DataTable data = new DataTable();  
                      adapter.Fill(data);  
                      return data;  
                   }  
               }  
                 
            }  

            /// <summary>  
            /// 执行一个查询语句，返回查询结果的第一行第一列  
            /// </summary>  
            /// <param name="sql">要执行的查询语句</param>  
            /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param>  
            /// <returns></returns>  
            public static Object ExecuteScalar(string sql, SQLiteParameter[] parameters)  
            {
                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + GetConfiguration("dataFile")))  
               {  
                  using (SQLiteCommand command = new SQLiteCommand(sql, connection))  
                  {  
                      if (parameters != null)  
                      {  
                           command.Parameters.AddRange(parameters);  
                      }  
                      SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);  
                      DataTable data = new DataTable();  
                      adapter.Fill(data);  
                      return data;  
                  }  
             }  
          }  

           /// <summary>  
           /// 查询数据库中的所有数据类型信息  
            /// </summary>  
           /// <returns></returns>  
            public static DataTable GetSchema()  
            {
                using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=" + GetConfiguration("dataFile")))  
                {  
                    connection.Open();  
                    DataTable data=connection.GetSchema("TABLES");  
                    connection.Close();  
                    //foreach (DataColumn column in data.Columns)  
                    //{  
                    //    Console.WriteLine(column.ColumnName);  
                    //}  
                   return data;  
                }  
            }  

    }
}
